﻿USE [HanhudDB]
GO
/*
	Cac store danh cho bang Properties
*/
IF(EXISTS(SELECT * FROM sys.objects WHERE [object_id] = OBJECT_ID('Aom.Properties_GetPropertyInfo') AND [type] IN (N'P')))
	DROP PROCEDURE Aom.Properties_GetPropertyInfo
GO
CREATE PROCEDURE Aom.Properties_GetPropertyInfo
	@PropertyID	VARCHAR(50)
AS
BEGIN
	SELECT     v.PropertyID, v.OwnerID, v.BrokerID, v.PropertyTypeID, v.LocationID, v.LocationFullName, v.ProvinceCode, v.CommuneCode, v.DistrictCode, v.PropertyFor, 
                      v.AddedDate, v.TotalSize, v.TotalBuildingSize, v.YearBuilt, v.NumOfBedRoom, v.NumOfRoom, v.[LotNo.], v.[PlotNo.], v.AskingPrice, v.ConstructionStatus, 
                      v.LegalStatus, v.StartDate, v.Title, v.[ExpireDate], v.[Description], v.ViewCount, v.LocationName, v.PropertyTypeName, v.TotalRating, v.HasPoint, v.Direction, v.[Status], 
                      v.[MapNo.], v.[Address]
	FROM         ViewProperties AS v
	WHERE     (v.PropertyID = @PropertyID)
END
GO
IF(EXISTS(SELECT * FROM sys.objects WHERE [object_id] = OBJECT_ID('Aom.Properties_AddProperty') AND [type] IN (N'P')))
	DROP PROCEDURE Aom.Properties_AddProperty
GO
CREATE PROCEDURE Aom.Properties_AddProperty
	@PropertyID		VARCHAR(50),
	@AddedDate		DATETIME,
	@PropertyTypeID	INT,
	@PropertyFor	INT,
	@MembershipID	UNIQUEIDENTIFIER,
	@BrokerID		UNIQUEIDENTIFIER,
	@LocationID		INT,
	@Address		NVARCHAR(500),
	/* Cac thuoc tinh bat buoc cua bat dong san */
	@Title			NVARCHAR(500),
	@Description	NVARCHAR(1000),
	@TotalSize		FLOAT,
	@TotalBuildingSize	FLOAT,
	@AskingPrice	FLOAT,
	--@MeasurePrice	NVARCHAR(50),
	@Direction		INT,
	@LegalStatus	INT,
	/* Cac thuoc tinh khong bat buoc */
	@YearBuilt		INT = -1,
	@NumOfRoom		INT = -1,
	@NumOfBedRoom	INT = -1,
	@MapNo			VARCHAR(50) = '',
	@LotNo			VARCHAR(50) = '',
	@PlotNo			VARCHAR(50) = '',
	@ConstructionStatus NVARCHAR(500) = N'',
	/* Ngay bat dau dua tin va ngay ket thuc dua tin */
	@StartDate		DATETIME,
	@ExpireDate		DATETIME
AS
BEGIN
	INSERT INTO Aom.Properties(PropertyID, AddedDate, PropertyTypeID, PropertyFor, MembershipID, BrokerID,
		LocationID, [Address], Title, [Description], TotalSize, TotalBuildingSize, AskingPrice, Direction, LegalStatus, 
		YearBuilt, NumOfRoom, NumOfBedRoom, [MapNo.], [LotNo.], [PlotNo.], ConstructionStatus,
		StartDate, ExpireDate)
	VALUES (@PropertyID, @AddedDate, @PropertyTypeID, @PropertyFor, @MembershipID, @BrokerID,
		@LocationID, @Address, @Title, @Description, @TotalSize, @TotalBuildingSize, @AskingPrice, @Direction, @LegalStatus,
		@YearBuilt, @NumOfRoom, @NumOfBedRoom, @MapNo, @LotNo, @PlotNo, @ConstructionStatus,
		@StartDate, @ExpireDate)
END
GO
IF(EXISTS(SELECT * FROM sys.objects WHERE [object_id] = OBJECT_ID('Aom.Properties_UpdateProperty') AND [type] IN (N'P')))
	DROP PROCEDURE Aom.Properties_UpdateProperty
GO
CREATE PROCEDURE Aom.Properties_UpdateProperty
	@PropertyID		VARCHAR(50),
	@PropertyTypeID	INT,
	@PropertyFor	INT,
	@BrokerID		UNIQUEIDENTIFIER,
	@LocationID		INT,
	@Address		NVARCHAR(500),
	/* Cac thuoc tinh bat buoc cua bat dong san */
	@Title			NVARCHAR(500),
	@Description	NVARCHAR(1000),
	@TotalSize		FLOAT,
	@TotalBuildingSize	FLOAT,
	@AskingPrice	FLOAT,
	--@MeasurePrice	NVARCHAR(50),
	@Direction		INT,
	@LegalStatus	INT,
	/* Cac thuoc tinh khong bat buoc */
	@YearBuilt		INT = -1,
	@NumOfRoom		INT = -1,
	@NumOfBedRoom	INT = -1,
	@MapNo			VARCHAR(50) = '',
	@LotNo			VARCHAR(50) = '',
	@PlotNo			VARCHAR(50) = '',
	@ConstructionStatus NVARCHAR(500) = N'',
	/* Ngay bat dau dua tin va ngay ket thuc dua tin */
	@StartDate		DATETIME,
	@ExpireDate		DATETIME
AS
BEGIN
	UPDATE Aom.Properties SET
		@PropertyFor	= PropertyFor,
		@BrokerID		= BrokerID,
		@LocationID		= LocationID,
		@Address		= Address,
		/* Cac thuoc tinh bat buoc cua bat dong san */
		@Title			= Title,
		@Description	= Description,
		@TotalSize		= TotalSize,
		@TotalBuildingSize = TotalBuildingSize,
		@AskingPrice	= AskingPrice,
		@Direction		= [Direction],
		@LegalStatus	= LegalStatus,
		/* Cac thuoc tinh khong bat buoc */
		@YearBuilt		= YearBuilt,
		@NumOfRoom		= NumOfRoom,
		@NumOfBedRoom	= NumOfBedRoom,
		@MapNo			= [MapNo.],
		@LotNo			= [LotNo.],
		@PlotNo			= [PlotNo.],
		@ConstructionStatus = ConstructionStatus
	WHERE PropertyID = @PropertyID
END
GO
IF(EXISTS(SELECT * FROM sys.objects WHERE [object_id] = OBJECT_ID('Aom.Properties_PropertyRenew') AND [type] IN (N'P')))
	DROP PROCEDURE Aom.Properties_PropertyRenew
GO
CREATE PROCEDURE Aom.Properties_PropertyRenew
	@PropertyID	VARCHAR(50),
	@RenewDate	DATETIME,
	@ExpireDate	DATETIME
AS
BEGIN
	UPDATE Aom.Properties SET
		StartDate	= @RenewDate,
		ExpireDate	= @ExpireDate
	WHERE PropertyID = @PropertyID
END
GO
IF(EXISTS(SELECT * FROM sys.objects WHERE [object_id] = OBJECT_ID('Aom.Properties_ChangePropertyStatus') AND [type] IN (N'P')))
	DROP PROCEDURE Aom.Properties_ChangePropertyStatus
GO
CREATE PROCEDURE Aom.Properties_ChangePropertyStatus
	@PropertyID		VARCHAR(50),
	@Status			INT
AS
BEGIN
	UPDATE Aom.Properties SET 
		[Status] = @Status
	WHERE PropertyID = @PropertyID
END
GO
IF(EXISTS(SELECT * FROM sys.objects WHERE [object_id] = OBJECT_ID('Aom.Properties_FilterProperties') AND [type] IN (N'P')))
	DROP PROCEDURE Aom.Properties_FilterProperties
GO
CREATE PROCEDURE Aom.Properties_FilterProperties
	@OwnerID		UNIQUEIDENTIFIER = NULL,
	@BrokerID		UNIQUEIDENTIFIER = NULL,
	@PropertyForList	INT = -1,
	@PropertyTypeList	INT = -1,
	@StartDate		DATETIME = NULL,
	@EndDate		DATETIME = NULL,
	@StatusList		INT = -1,
	@Keyword		NVARCHAR(256) = N'',
	@ExactWord		BIT = 0,
	@ProvinceCode	INT = -1,
	@DistrictCode	INT = -1,
	@CommuneCode	INT = -1,
	@FromPrice		FLOAT = -1,
	@ToPrice		FLOAT = -1,
	@FromSize		FLOAT = -1,
	@ToSize			FLOAT = -1,
	@FromNumOfBedRoom	INT = -1,
	@ToNumOfBedRoom		INT = -1,
	@DirectionList		INT = 1,
	-- Phan trang
	@PageIndex		INT,
	@PageSize		INT,
	-- Total ResultCount 
	@ResultCount	INT OUTPUT
AS
BEGIN
	DECLARE @PageLowerBound INT
		SET @PageLowerBound = (@PageIndex - 1) * @PageSize
	DECLARE @PageUpperBound	INT
		SET @PageLowerBound = @PageLowerBound + @PageSize - 1;
		
	DECLARE @FilterProperties TABLE
	(
		IndexID		INT,
		PropertyID	VARCHAR(50),
		ResultCount	INT
	)
	
	DECLARE @CONST INT
		SET @CONST = 1
		
	-- LAM SAU
	;WITH FilterProperties(IndexID, PropertyID, ResultCount) AS
	(
		SELECT ROW_NUMBER() OVER(ORDER BY @CONST) AS IndexID, p.PropertyID, COUNT(p.PropertyID) OVER() AS ResultCount
		FROM Aom.Properties AS p INNER JOIN
			Locations AS l ON l.LocationID = p.LocationID
		WHERE
			((@StatusList = -1) OR (p.[Status] & @StatusList) = p.[Status])							-- Loc theo trang thai
		AND ((@OwnerID IS NULL) OR (p.OwnerID = @OwnerID))										-- Loc theo chu so huu
		AND ((@BrokerID IS NULL) OR (p.BrokerID = @BrokerID))										-- Loc theo nha moi gioi
		AND	((@PropertyForList = -1) OR (@PropertyForList & p.PropertyFor) = p.PropertyFor)			-- Loc theo muc dich
		AND ((@PropertyTypeList = -1) OR (@PropertyTypeList & p.PropertyTypeID) = p.PropertyFor)	-- Loc theo kieu bat dong san
		AND ((@ProvinceCode = -1) OR (l.ProvinceCode = @ProvinceCode))								-- Loc theo Tinh/Thanh
		AND	((@DistrictCode = -1 ) OR (l.DistrictCode = @DistrictCode))								-- Loc theo Quan/Huyen
		AND ((@CommuneCode = -1) OR (l.CommuneCode = @CommuneCode))									-- Loc theo Phuong/Xa
		AND ((@FromPrice = -1) OR (p.AskingPrice >= @FromPrice))
		AND ((@ToPrice <= @FromPrice)OR (p.AskingPrice <= @ToPrice))
		AND ((@FromSize = -1) OR (p.TotalSize >= @FromSize))
		AND ((@ToSize <= @FromSize) OR (p.TotalSize <= @ToPrice))
		AND ((@FromNumOfBedRoom = -1) OR (p.NumOfBedRoom >= @FromNumOfBedRoom))
		AND ((@ToNumOfBedRoom <= @FromNumOfBedRoom) OR (p.NumOfBedRoom <= @ToNumOfBedRoom))
		AND ((@DirectionList = 1) OR (@DirectionList & p.Direction) = p.Direction)
		AND ((DATALENGTH(@Keyword) < 3) OR 
				(		((@ExactWord = 1) OR (LOWER(p.Title) LIKE '%' + @Keyword + '%')) 
					AND ((@ExactWord = 0) OR CONTAINS(p.Title, @Keyword))  ))
		ORDER BY p.StartDate DESC
	)
	
	-- Lua vao bang tam
	INSERT INTO @FilterProperties(IndexID, PropertyID, ResultCount)
		SELECT PropertyID, ResultCount 
		FROM FilterProperties AS fp
		WHERE	fp.IndexID BETWEEN @PageLowerBound AND @PageUpperBound
	
	-- Tra lai tong so dong
	SELECT TOP 1 @ResultCount = ResultCount FROM @FilterProperties
	
	-- Ket qua
	SELECT     v.PropertyID, v.OwnerID, v.BrokerID, v.PropertyTypeID, v.LocationID, v.LocationFullName, v.ProvinceCode, v.CommuneCode, v.DistrictCode, v.PropertyFor, 
                      v.AddedDate, v.TotalSize, v.TotalBuildingSize, v.YearBuilt, v.NumOfBedRoom, v.NumOfRoom, v.[LotNo.], v.[PlotNo.], v.AskingPrice, v.ConstructionStatus, 
                      v.LegalStatus, v.StartDate, v.Title, v.[ExpireDate], v.[Description], v.ViewCount, v.LocationName, v.PropertyTypeName, v.TotalRating, v.HasPoint, v.Direction, v.[Status], 
                      v.[MapNo.], v.[Address]
	FROM         ViewProperties AS v INNER JOIN
						  @FilterProperties fp ON fp.PropertyID = v.PropertyID
END
GO
IF(EXISTS(SELECT * FROM sys.objects WHERE [object_id] = OBJECT_ID('Aom.Properties_GetPropertyPositionOnMap') AND [type] IN (N'P')))
	DROP PROCEDURE Aom.Properties_GetPropertyPositionOnMap
GO
CREATE PROCEDURE Aom.Properties_GetPropertyPositionOnMap
	@PropertyID		VARCHAR(50)	
AS
BEGIN
	SELECT     PropertyID, LatLng, Zoom, GMapType
	FROM         GPoints
	WHERE     (PropertyID = @PropertyID)
END
GO
IF(EXISTS(SELECT * FROM sys.objects WHERE [object_id] = OBJECT_ID('Aom.Properties_GetPropertiesOnMap') AND [type] IN (N'P')))
	DROP PROCEDURE Aom.Properties_GetPropertiesOnMap
GO
CREATE PROCEDURE Aom.Properties_GetPropertiesOnMap
	@BoundText		VARCHAR(2000),
	@BrokerID		UNIQUEIDENTIFIER = NULL,
	@PropertyForList	INT = -1,
	@PropertyTypeList	INT = -1,
	-- Phan trang
	@PageIndex		INT,
	@PageSize		INT,
	-- Tong ket qua
	@ResultCount	INT
AS
BEGIN
	DECLARE @Bound	GEOMETRY
		SET @Bound = GEOMETRY::STGeomFromText(@BoundText, 0)
		
	DECLARE @PageLowerBound INT
		SET @PageLowerBound = (@PageIndex - 1) * @PageSize
	DECLARE @PageUpperBound	INT
		SET @PageLowerBound = @PageLowerBound + @PageSize - 1;
		
	DECLARE @FilterProperties TABLE
	(
		IndexID		INT,
		PropertyID	VARCHAR(50),
		ResultCount	INT
	)
	
	DECLARE @CONST INT
		SET @CONST = 1
		
	;WITH FilterProperties(IndexID, PropertyID, ResultCount) AS
	(
		SELECT ROW_NUMBER() OVER(ORDER BY @CONST) AS IndexID, p.PropertyID, COUNT(p.PropertyID) OVER() AS ResultCount
		FROM Aom.Properties AS p INNER JOIN 
			GPoints AS gp ON p.PropertyID = gp.PropertyID
		WHERE 
				@Bound.STContains(p.LatLng)
			AND v.[Status] = 2		-- Chi xet cac truong hop property da duoc duyet
		ORDER BY v.StartDate DESC
	
	)
	
	-- Lua vao bang tam
	INSERT INTO @FilterProperties(IndexID, PropertyID, ResultCount)
		SELECT PropertyID, ResultCount 
		FROM FilterProperties AS fp
		WHERE	fp.IndexID BETWEEN @PageLowerBound AND @PageUpperBound
	
	-- Tra lai tong so dong
	SELECT TOP 1 @ResultCount = ResultCount FROM @FilterProperties
		
	-- Ket qua
	SELECT     v.PropertyID, v.OwnerID, v.BrokerID, v.PropertyTypeID, v.LocationID, v.LocationFullName, v.ProvinceCode, v.CommuneCode, v.DistrictCode, v.PropertyFor, 
                      v.AddedDate, v.TotalSize, v.TotalBuildingSize, v.YearBuilt, v.NumOfBedRoom, v.NumOfRoom, v.[LotNo.], v.[PlotNo.], v.AskingPrice, v.ConstructionStatus, 
                      v.LegalStatus, v.StartDate, v.Title, v.[ExpireDate], v.[Description], v.ViewCount, v.LocationName, v.PropertyTypeName, v.TotalRating, v.HasPoint, v.Direction, v.[Status], 
                      v.[MapNo.], v.[Address]
	FROM         ViewProperties AS v INNER JOIN
						  @FilterProperties fp ON fp.PropertyID = v.PropertyID
END
GO
/* PropertyPhotos */
IF(EXISTS(SELECT * FROM sys.objects WHERE [object_id] = OBJECT_ID('Aom.PropertyPhotos_AddPropertyPhoto') AND [type] IN (N'P')))
	DROP PROCEDURE Aom.PropertyPhotos_AddPropertyPhoto
GO
CREATE PROCEDURE Aom.PropertyPhotos_AddPropertyPhoto
	@PhotoID		VARCHAR(50),
	@PropertyID		VARCHAR(50),
	@AddedDate		DATETIME,
	@Title			NVARCHAR(256),
	@ThumbFileName	NVARCHAR(500),
	@LargeFileName	NVARCHAR(500),
	@IsDefault		BIT = 0
AS
BEGIN
	DECLARE @ErrorCode INT
	SET @ErrorCode = 0

    DECLARE @TranStarted   bit
    SET @TranStarted = 0

    IF( @@TRANCOUNT = 0 )
    BEGIN
	    BEGIN TRANSACTION
	    SET @TranStarted = 1
    END
    ELSE
    	SET @TranStarted = 0
	
	-- Lay PhotoCategory danh cho Property
	DECLARE @PropertyPhotoCategoryID INT
	SELECT TOP 1 @PropertyPhotoCategoryID = PropertyPhotoCategoryID FROM Settings
	
	IF(@IsDefault = 1) BEGIN
		UPDATE Aom.PropertyPhotos SET
			IsDefault = 0
		WHERE PropertyID = @PropertyID
	END
	
	INSERT INTO Photos(PhotoID, AddedDate, PhotoCategoryID, Title, ThumbFileName, LargeFileName)
	VALUES (@PhotoID, @AddedDate, @PropertyPhotoCategoryID, @Title, @ThumbFileName, @LargeFileName)
	
	IF(@@ERROR <> 0) BEGIN
		SET @ErrorCode = @@ERROR
		GOTO CleanUp
	END
	
	INSERT INTO Aom.PropertyPhotos(PhotoID, PropertyID, IsDefault)
	VALUES (@PhotoID, @PropertyID, @IsDefault)
	
	IF(@@ERROR <> 0) BEGIN
		SET @ErrorCode = @@ERROR
		GOTO CleanUp
	END
	
	IF( @TranStarted = 1 )
    BEGIN
	    SET @TranStarted = 0
	    COMMIT TRANSACTION
    END

    RETURN 0
	
CleanUp:
	IF( @TranStarted = 1 )
    BEGIN
        SET @TranStarted = 0
    	ROLLBACK TRANSACTION
    END

    RETURN @ErrorCode
END
GO
IF(EXISTS(SELECT * FROM sys.objects WHERE [object_id] = OBJECT_ID('Aom.PropertyPhotos_GetPropertyPhotos') AND [type] IN (N'P')))
	DROP PROCEDURE Aom.PropertyPhotos_GetPropertyPhotos
GO
CREATE PROCEDURE Aom.PropertyPhotos_GetPropertyPhotos
	@PropertyID	VARCHAR(50)
AS
BEGIN
	SELECT     Aom.PropertyPhotos.PhotoID, Aom.PropertyPhotos.ProductID, Photos.AddedDate, Photos.Title, Photos.ThumbFileName, Photos.LargeFileName, 
                      Aom.PropertyPhotos.IsDefault
	FROM         Aom.PropertyPhotos INNER JOIN
						  Photos ON Aom.PropertyPhotos.PhotoID = Photos.PhotoID
	WHERE     (Aom.PropertyPhotos.ProductID = @PropertyID)
END
GO
IF(EXISTS(SELECT * FROM sys.objects WHERE [object_id] = OBJECT_ID('Aom.PropertyPhotos_DeletePropertyPhotos') AND [type] IN (N'P')))
	DROP PROCEDURE Aom.PropertyPhotos_DeletePropertyPhotos
GO
CREATE PROCEDURE Aom.PropertyPhotos_DeletePropertyPhotos
	@PhotoIDList	VARCHAR(1000)
AS
BEGIN
	DECLARE @ErrorCode INT
	SET @ErrorCode = 0

    DECLARE @TranStarted   bit
    SET @TranStarted = 0

    IF( @@TRANCOUNT = 0 )
    BEGIN
	    BEGIN TRANSACTION
	    SET @TranStarted = 1
    END
    ELSE
    	SET @TranStarted = 0
    
    DECLARE @DeletePhotoIDList TABLE
    (
		PhotoID		VARCHAR(50)
    )
    
    INSERT INTO @DeletePhotoIDList (PhotoID)
    SELECT ItemId FROM dbo.Split(@PhotoIDList, ',')
    
    DELETE Aom.PropertyPhotos WHERE PhotoID IN (SELECT PhotoID FROM @DeletePhotoIDList)
    
    IF(@@ERROR <> 0) BEGIN
		SET @ErrorCode = @@ERROR
		GOTO CleanUp
    END
    
    DELETE Photos WHERE PhotoID IN (SELECT PhotoID FROM @DeletePhotoIDList)
    
    IF(@@ERROR <> 0) BEGIN
		SET @ErrorCode = @@ERROR
		GOTO CleanUp
    END
    
	IF( @TranStarted = 1 )
    BEGIN
	    SET @TranStarted = 0
	    COMMIT TRANSACTION
    END

    RETURN 0
	
CleanUp:
	IF( @TranStarted = 1 )
    BEGIN
        SET @TranStarted = 0
    	ROLLBACK TRANSACTION
    END

    RETURN @ErrorCode
	
END
GO
/* Property highlights */
IF(EXISTS(SELECT * FROM sys.objects WHERE [object_id] = OBJECT_ID('Aom.Properties_GetPropertyHighlights') AND [type] IN (N'P')))
	DROP PROCEDURE Aom.Properties_GetPropertyHighlights
GO
CREATE PROCEDURE Aom.Properties_GetPropertyHighlights
	@PropertyID	VARCHAR(50)
AS
BEGIN
	SELECT     Title
	FROM         Aom.Highlights
	WHERE     (PropertyID = @PropertyID)
END
GO
/*
	Cac store danh cho bang Projects
*/